from os import path
from typing import Literal, Dict
import pandas as pd
from pandas import DataFrame, Series
import numpy as np

from .singleton import singleton
from .mydb import SQL, Table

sql = SQL()


class DF_Table(Table):
    def __init__(self, tb_name, col_list=None):
        super().__init__(tb_name)
        self.key_col = "once_id"
        if col_list:
            self.col_list: list = col_list

    def data_dic_to_series(self, data_dic: Dict) -> Series:
        """将字典数据转为序列"""
        if not data_dic or not self.col_list:
            return Series()
        data_list = []
        dic_key = data_dic.keys()

        for col in self.col_list:
            if col in dic_key:
                data_list.append(data_dic[col])
            else:
                data_list.append(None)

        return Series(data_list, index=self.col_list)

    def df_data_to_table(self, df_data: DataFrame, is_exists="append"):
        df_to_table(self.name, df_data, is_exists)

    def look_up_data(self, find_str):
        if find_str:
            condition = f" look_up_str like '{find_str}'"
        get_tb_data(self.name, condition)

    def file_dict_list_to_table(self, file_dict_list: list):
        df_file_all = pd.DataFrame(file_dict_list)
        col_list = self.col_list
        if "id" in col_list:
            col_list = self.col_list[1:]
        diff = set(col_list) - set(df_file_all.columns.to_list())
        if diff:
            for col in diff:
                df_file_all.loc[:, col] = np.nan
        file_df = df_file_all[col_list]
        self.df_data_to_table(file_df)


def get_col_values(df: DataFrame, col_name, first_value=""):
    """
    获取df 所指定列名中列值的唯一值，first_value是设置首值
    """
    df_list = df[col_name].unique().tolist()
    col_list = []
    if df_list:
        if len(df_list) == 1:
            col_list.append(df_list[0].strip())
            return col_list
        if len(df_list) > 1:
            if first_value:
                col_list.append(first_value)
            for data in df_list:
                data = data.strip()
                if data not in col_list:
                    col_list.append(data)
    return col_list


def df_to_table(table_name, df_data: DataFrame, if_exists: Literal["fail", "replace", "append"] = "replace"):
    """
    if_exists:
        fail：如果表已存在，则引发一个 ValueError 异常
        replace:如果表已存在，则先删除表，然后再重新创建。
        append：如果表已存在，则将数据追加到表中。
    """
    result = df_data.to_sql(table_name, sql.conn, if_exists=if_exists, index=False)
    return result


# 获取数据库中的数据表数据
def get_tb_data(table_name, condition=None, count=500):
    sql_str = f"select * from {table_name}"
    if condition:
        sql_str = sql_str + f" where {condition}"
    return __get_query_data(sql_str, count)


def get_tb_datas_counts(table_name, count: int):
    sql_str = f"select * from {table_name}  LIMIT {count}"
    return __get_query_data(sql_str)


# 获取查询语句中的数据
def __get_query_data(sql_str, count=500) -> DataFrame:
    """获取查询语句中的数据"""
    df_data = pd.read_sql(sql_str, sql.conn).head(500)
    return df_data


def excel_to_db(tb_name, excel_path, sheet_name=None, if_exists="replace"):
    """
    将excel的信息写入数据库
    tb_name: 数据表名
    excel_path:excel文件地址
    sheet_name:Excel表名
    if_exists:如果数据表存在的处理方式 [replace:替换  append:后面追加]
    """
    df_data = pd.read_excel(excel_path, sheet_name)
    result = df_to_table(tb_name, df_data, if_exists)
    return result


def df_to_excel_sheet(df, file_path, sheet_name):
    """将数据写入Excel的表格中"""
    with pd.ExcelWriter(file_path, mode='a', if_sheet_exists='replace') as writer:
        df.to_excel(writer, sheet_name=sheet_name)


def df_to_excel(df: DataFrame, file_path, sheet_name=None):
    """将数据写入Excel 或Excel表单中"""
    if path.isfile(file_path) and sheet_name:
        df_to_excel_sheet(df, file_path, sheet_name)
        return
    df.to_excel(file_path, index=True)


def df_to_col_unique_values(df: DataFrame, col_name):
    return df[col_name].unique().tolist()


# 将数据字典更细至数据库
def change_dict_to_db(table_name, change_dict: dict, key_col_name):
    """
    # 将数据字典更细至数据库
    change_dict:变化的数据字典，格式{key_col_value:{col_name:value},}
    key_col_name:关键列名
    table_name:表名
    """
    if change_dict:
        table = DF_Table(table_name)
        keys = change_dict.keys()
        for key in keys:
            data: dict = change_dict[key]
            col_name_list = data.keys()
            values = data.values()
            if key_col_name == "id":
                table.update_row(values, f" {key_col_name} = {key}", col_name_list)
            else:
                table.update_row(values, f" {key_col_name} = '{key}'", col_name_list)
